﻿using DevExpress.Utils;
using DevExpress.XtraGrid.Views.Grid;
using RecruitStudents.Utility;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Json;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace RecruitStudents.Client.Examinee
{
    public partial class Examinee_ImportStudentsForm_List : BaseForm
    {
        DataTable dtSpec = null;
        DataTable dtExamProvince = null;
        DataTable dtExamPlace = null;
        DataSet dsExcel = null;
        private const string ExcelFiled = "高考报名号,姓名,身份证号,生源地,考点,专业代码,专业名称,联系电话,联系手机,联系人,邮政编码,邮寄地址,性别,生日,民族,政治面貌,户口所在地,毕业学校,备注";
        private List<string> listExcelFiled = null;

        BLL.ServiceComm commService = new BLL.ServiceComm();
        BLL.ServiceExaminee examService = new BLL.ServiceExaminee();
        public Examinee_ImportStudentsForm_List()
        {
            InitializeComponent();
            btnValidate.Enabled = false;
            btnImport.Enabled = false;
            listExcelFiled = new List<string>();
            listExcelFiled.AddRange(ExcelFiled.Split(','));
            dtSpec = commService.GetSpecialityKindsTable(SessionerHelper.SystemYear);
            dtExamProvince = commService.GetDicDataTableByDicTableName("TB_Dic_ExamProvince");
            dtExamPlace = commService.GetExamPlaceTable();
        }

        private void btnView_Click(object sender, EventArgs e)
        {
            //初始化一个OpenFileDialog类
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
            //判断用户是否正确的选择了文件
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text = fileDialog.FileName;
                //连接字符串   //03版 Microsoft.Jet.OLEDB.4.0  
                const string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='{1};HDR=Yes;IMEX=1'";

                OleDbConnection conn = new OleDbConnection(string.Format(strConn, txtPath.Text, "Excel 8.0"));
                try
                {
                    conn.Open();
                }
                catch
                {
                    conn = new OleDbConnection(string.Format(strConn, txtPath.Text, "Excel 12.0"));
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ee)
                    {
                        DevHelper.ShowErrorMessageBox("连接Excel文件错误:" + ee.Message);
                        return;
                    }
                }
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                dsExcel = new DataSet();
                foreach (DataRow dr in dt.Rows)
                {
                    string sql = "select * from [" + dr["TABLE_NAME"].ToString() + "]";
                    OleDbDataAdapter aper = new OleDbDataAdapter(sql, conn);
                    aper.Fill(dsExcel, dr["TABLE_NAME"].ToString());
                    if (dsExcel.Tables[dr["TABLE_NAME"].ToString()].Columns.Count < 2)
                    {
                        continue;
                    }
                    if (dsExcel == null || dsExcel.Tables[0].Rows.Count < 1)
                    {
                        dsExcel = null;
                        conn.Close();
                        conn.Dispose();
                        aper.Dispose();
                        DevHelper.ShowErrorMessageBox("预览数据错误:没有任何数据进行预览");
                        return;
                    }
                }
                conn.Close();
                conn.Dispose();
                foreach (string strExcelFiled in listExcelFiled)
                {
                    if (!dsExcel.Tables[0].Columns.Contains(strExcelFiled))
                    {
                        dsExcel = null;
                        DevHelper.ShowErrorMessageBox("导入数据错误:导入的Excel模板数据与系统模板不符合，请下载模板再进行导入！");
                        return;
                    }
                }
                gridControl1.DataSource = dsExcel.Tables[0];
                btnValidate.Enabled = true;
            }
        }

        private void btnDown_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
            sfd.FileName = "考生信息导入" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            if (sfd.ShowDialog(this) == DialogResult.OK)
            {
                string fileName = Application.StartupPath + "\\Report\\考生信息导入.xls";
                if (File.Exists(fileName))
                {
                    File.Copy(fileName, sfd.FileName, true);
                    DevHelper.ShowInfoMessageBox("下载成功！");
                }
                else
                {
                    DevHelper.ShowErrorMessageBox("未找到考生信息模板文件！");
                    return;
                }
            }
        }

        void gridView1_CustomDrawRowIndicator(object sender, RowIndicatorCustomDrawEventArgs e)
        {
            e.Appearance.TextOptions.HAlignment = HorzAlignment.Far;
            if (e.Info.IsRowIndicator && e.RowHandle >= 0)
            {
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
            }
        }
        protected override void OnLoad(EventArgs e)
        {
            gridView1.IndicatorWidth = 40;
            gridView1.PreviewIndent = 0;
            gridView1.CustomDrawRowIndicator += gridView1_CustomDrawRowIndicator;
            base.OnLoad(e);
        }

        private void btnValidate_Click(object sender, EventArgs e)
        {
            try
            {
                if (dsExcel == null || dsExcel.Tables[0].Rows.Count < 1)
                {
                    DevHelper.ShowErrorMessageBox("验证数据出现错误:没有任何数据进行验证，请先选择文件进行预览");
                    return;
                }
                foreach (string strExcelFiled in listExcelFiled)
                {
                    if (!dsExcel.Tables[0].Columns.Contains(strExcelFiled))
                    {
                        gridControl1.DataSource = null;
                        dsExcel = null;
                        DevHelper.ShowErrorMessageBox("数据校验失败:导入的Excel模板数据与系统模板不符合，请下载模板再进行导入！");
                        return;
                    }
                }
                Dictionary<string, TB_SignUpStudents> dicSignUpStudent = new Dictionary<string, TB_SignUpStudents>();
                List<TB_Exam_SignUpStudentsSpeciality> listSpec = new List<TB_Exam_SignUpStudentsSpeciality>();

                List<string> listBKZY = new List<string>();
                TB_SignUpStudents model = null;
                DataTable dtSignUp = null;
                string strSignUpID = string.Empty;
                string strNMT_SignUpID = string.Empty;
                foreach (DataRow dr in dsExcel.Tables[0].Rows)
                {
                    strSignUpID = string.Empty;
                    strNMT_SignUpID = dr["高考报名号"].ToSafeString();
                    dtSignUp = examService.GetExameeByNMT_SignUpID(strNMT_SignUpID);
                    if (dtSignUp == null || dtSignUp.Rows.Count == 0)
                    {
                        if (!dicSignUpStudent.ContainsKey(strNMT_SignUpID))
                        {
                            #region 考生基本信息
                            model = new TB_SignUpStudents();
                            model.ExamYear = SessionerHelper.SystemYear;
                            model.SignUpDatetime = DateTime.Now;
                            model.Name = dr["姓名"].ToSafeString();
                            model.NMT_SignUpID = strNMT_SignUpID;
                            model.IdentityCard = dr["身份证号"].ToSafeString();

                            DataRow[] drs = dtExamProvince.Select("DicNO='" + dr["生源地"].ToSafeString().Trim() + "' or DicName='" + dr["生源地"].ToSafeString().Trim() + "'");
                            if (drs.Length == 0)
                            {
                                DevHelper.ShowErrorMessageBox("数据校验失败:姓名 " + model.Name + "  的生源地出现异常！");
                                return;
                            }
                            else
                            {
                                model.ExamProvince = drs[0]["DicNO"].ToSafeString();
                            }

                            model.LinkMobile = dr["联系电话"].ToSafeString();
                            model.LinkTelphone = dr["联系手机"].ToSafeString();
                            model.LinkMan = dr["联系人"].ToSafeString();
                            model.LinkPostalCode = dr["邮政编码"].ToSafeString();
                            model.CertificatePostAddress = dr["邮寄地址"].ToSafeString();
                            if (dr["性别"].ToSafeString().Trim() == "男")
                            {
                                model.Sex = "1";
                            }
                            else if (dr["性别"].ToSafeString().Trim() == "女")
                            {
                                model.Sex = "2";
                            }
                            else
                            {
                                model.Sex = dr["性别"].ToSafeString();
                            }
                            if (dr["生日"].ToSafeString().Trim() == "")
                            {
                                if (model.IdentityCard.Length == 18)//处理18位的身份证号码从号码中得到生日和性别代码
                                {
                                    model.Birth = model.IdentityCard.Substring(6, 4) + "-" + model.IdentityCard.Substring(10, 2) + "-" + model.IdentityCard.Substring(12, 2);
                                }
                                else if (model.IdentityCard.Length == 15)
                                {
                                    model.Birth = "19" + model.IdentityCard.Substring(6, 2) + "-" + model.IdentityCard.Substring(8, 2) + "-" + model.IdentityCard.Substring(10, 2);
                                }
                            }
                            else
                            {
                                model.Birth = dr["生日"].ToSafeString();
                            }
                            model.Nation = dr["民族"].ToSafeString();
                            model.Party = dr["政治面貌"].ToSafeString();
                            model.RPR_Place = dr["户口所在地"].ToSafeString();
                            model.WorkOrStudyPlace = dr["毕业学校"].ToSafeString();
                            model.Remark = dr["备注"].ToSafeString();
                            dicSignUpStudent.Add(model.NMT_SignUpID, model);
                            #endregion
                        }
                        else
                        {
                            model = dicSignUpStudent[strNMT_SignUpID];
                        }
                        strSignUpID = model.SignUpID;
                    }
                    else
                    {
                        strSignUpID = dtSignUp.Rows[0]["SignUpID"].ToSafeString();
                        strNMT_SignUpID = dtSignUp.Rows[0]["NMT_SignUpID"].ToSafeString();
                        if (!dicSignUpStudent.ContainsKey(strNMT_SignUpID))
                        {
                            model = new TB_SignUpStudents();
                            model.SignUpID = strSignUpID;
                            model.NMT_SignUpID = strNMT_SignUpID;
                            dicSignUpStudent.Add(model.NMT_SignUpID, model);
                        }
                    }
                    string strBKZYDM = dr["专业代码"].ToSafeString();
                    DataRow[] drSpecs = dtSpec.Select("SpecialityKindsID='" + strBKZYDM + "'");
                    if (drSpecs.Length == 0)
                    {
                        DevHelper.ShowErrorMessageBox("数据校验失败:考生 " + dr["姓名"].ToSafeString() + " 填报的专业代码在不存在！");
                        return;
                    }
                    string strExamPlaceNO = string.Empty;
                    DataRow[] drsPlace = dtExamPlace.Select("ExamPlaceNO='" + dr["考点"].ToSafeString() + "' or ExamPlaceName='" + dr["考点"].ToSafeString() + "'");
                    if (drsPlace.Length == 0)
                    {
                        DevHelper.ShowErrorMessageBox("数据校验失败:考生 " + dr["姓名"].ToSafeString() + " 填报的考点不存在！");
                        return;
                    }
                    strExamPlaceNO = drsPlace[0]["ExamPlaceNO"].ToSafeString();
                    string strKey = strNMT_SignUpID + "&" + strBKZYDM + "&" + strExamPlaceNO;
                    if (!listBKZY.Contains(strKey))
                    {
                        listBKZY.Add(strKey);
                    }
                    else
                    {
                        DevHelper.ShowErrorMessageBox("数据校验失败:同一考生在同一考点不能报相同的专业考试");
                        return;
                    }
                    if (drSpecs.Length > 0)
                    {
                        TB_Exam_SignUpStudentsSpeciality modelSpec = null;
                        foreach (DataRow drSpec in drSpecs)
                        {
                            modelSpec = new TB_Exam_SignUpStudentsSpeciality();
                            modelSpec.IsFee_First = "1";
                            modelSpec.CreatTime = DateTime.Now;
                            modelSpec.ExamLevel = 1;
                            modelSpec.ExamPlaceNO = strExamPlaceNO;
                            modelSpec.ExamYear = SessionerHelper.SystemYear;
                            modelSpec.IsActive = "1";
                            modelSpec.IsPass = "0";
                            modelSpec.SignUpID = strSignUpID;
                            modelSpec.SpecialityKindsNO = drSpec["SpecialityKindsNO"].ToSafeString();
                            listSpec.Add(modelSpec);
                        }
                    }
                    else
                    {
                        DevHelper.ShowErrorMessageBox("数据校验失败:考生报考的专业代码在系统中不存在！");
                        return;
                    }
                }
                if (listSpec.Count == 0)
                {
                    strSignUpStudentJson = string.Empty;
                    strSignUpStudentSpecJson = string.Empty;
                    if (dicSignUpStudent.Count == 0)
                    {
                        DevHelper.ShowErrorMessageBox("校验考生数据为0！");
                    }
                    if (listSpec.Count == 0)
                    {
                        DevHelper.ShowErrorMessageBox("校验考生报考专业数据为0！");
                    }
                    return;
                }
                else
                {
                    strSignUpStudentJson = JsonHelper.ObjectToJson(dicSignUpStudent);
                    strSignUpStudentSpecJson = JsonHelper.ObjectToJson(listSpec);
                    btnImport.Enabled = true;
                    DevHelper.ShowInfoMessageBox("数据校验成功！");
                }
            }
            catch (Exception ex)
            {
                strSignUpStudentJson = string.Empty;
                strSignUpStudentSpecJson = string.Empty;
                DevHelper.ShowErrorMessageBox("数据校验失败:" + ex.Message);
                return;
            }
        }

        private string strSignUpStudentJson = string.Empty;
        private string strSignUpStudentSpecJson = string.Empty;

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (strSignUpStudentJson != string.Empty && strSignUpStudentSpecJson != string.Empty)
            {
                Dictionary<string, TB_SignUpStudents> dicSignUpStudentNew = null;
                List<TB_Exam_SignUpStudentsSpeciality> listSpecNew = null;
                try
                {
                    dicSignUpStudentNew = JsonHelper.JsonToObject<Dictionary<string, TB_SignUpStudents>>(strSignUpStudentJson);
                    listSpecNew = JsonHelper.JsonToObject<List<TB_Exam_SignUpStudentsSpeciality>>(strSignUpStudentSpecJson);

                    Dictionary<string, TB_SignUpStudents> dicNew = null;
                    List<TB_Exam_SignUpStudentsSpeciality> listNew = null;
                    if (dicSignUpStudentNew != null && dicSignUpStudentNew.Count > 0)
                    {
                        foreach (KeyValuePair<string, TB_SignUpStudents> item in dicSignUpStudentNew)
                        {
                            listNew = new List<TB_Exam_SignUpStudentsSpeciality>();
                            dicNew = new Dictionary<string, TB_SignUpStudents>();
                            dicNew.Add(item.Key, item.Value);


                            //List<TB_Exam_SignUpStudentsSpeciality> it = listSpecNew.Where(x => x.SignUpID == item.Value.SignUpID).ToList();


                            foreach (TB_Exam_SignUpStudentsSpeciality model in listSpecNew)
                            {
                                if (model.SignUpID == item.Value.SignUpID)
                                {
                                    listNew.Add(model);
                                }
                            }
                            examService.ImportSignUpStudents(JsonHelper.ObjectToJson(dicNew), JsonHelper.ObjectToJson(listNew));
                        }
                    }
                    else
                    {
                        listNew = new List<TB_Exam_SignUpStudentsSpeciality>();
                        foreach (TB_Exam_SignUpStudentsSpeciality model in listSpecNew)
                        {
                            listNew.Add(model);
                        }
                        examService.ImportSignUpStudents(JsonHelper.ObjectToJson(dicSignUpStudentNew), JsonHelper.ObjectToJson(listNew));
                    }
                    strSignUpStudentJson = strSignUpStudentSpecJson = string.Empty;
                    DevHelper.ShowInfoMessageBox("导入考生数据成功！");
                    this.Close();
                }
                catch (Exception ex)
                {
                    DevHelper.ShowErrorMessageBox("导入数据出现错误:" + ex.Message);
                    return;
                }
            }
            else
            {
                DevHelper.ShowErrorMessageBox("数据导入失败:请先进行数据校验！");
                return;
            }
        }
    }
}
